Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Restoring and Recovering the Auxiliary Databas in User-Managed TSPITR: Basic Steps

The procedure for restore and recovery of the auxiliary database differs depending on whether the auxiliary database is on the same host as the primary database. The examples in this section assume:

- You are performing TSPITR on production database called `prod1` located on host `prim_host`.

- The recovery set tablespaces are `users` and `tools`. Tablespace `users` contains datafile `/oracle/oradata/users01.dbf` and tablespace `tools` contains datafile `/fs2/tools01.dbf`.

- The auxiliary set contains the `SYSTEM` tablespace datafile `/oracle/oradata/system.dbf`, the undo tablespace datafile `/oracle/oradata/undo01.dbf`, and the control file `/oracle/oradata/control01.dbf`.

- The online redo logs are named `/oracle/oradata/redo01.log` and `/oracle/oradata/redo02.log`.

- All the primary database files are contained in `/oracle/oradata`

The different cases are described in the following sections:

- Restoring and Recovering the Auxiliary Database on the Same Host

- Restoring the Auxiliary Database on a Different Host with the Same Path Names

- Restoring the Auxiliary Database on a Different Host with Different Path Names

## Restoring and Recovering the Auxiliary Database on the Same Host

The following examples assume the case in which you restore the auxiliary database to the same host as the primary database. In this scenario, all of the primary database files are contained in `/oracle/oradata`, and you want to restore the auxiliary database to `/oracle/oradata/aux`. So, you set `DB_FILE_NAME_CONVERT` and `LOG_FILE_NAME_CONVERT` to convert the filenames from `/oracle/oradata` to `/oracle/oradata/aux`.

Perform the following tasks to restore and recover the auxiliary database:

1. Restore the auxiliary set and the recovery set to a location different from that of the primary database. For example, assume that the auxiliary set consists of the following files:

       /oracle/oradata/control01.dbf    # control file
       /oracle/oradata/undo01.dbf       # datafile in undo tablespace
       /oracle/oradata/system.dbf       # datafile in SYSTEM tablespace

   And the recovery set consists of the following datafiles:

       /oracle/oradata/users01.dbf  # datafile in users tablespace
       /oracle/oradata/tools01.dbf  # datafile in tools tablespace

   You can restore backups of the auxiliary set files and recovery set files to a new location as follows:

       cp /backup/control01.dbf /oracle/oradata/aux/control01.dbf
       cp /backup/undo01.dbf /oracle/oradata/aux/undo01.dbf
       cp /backup/system.dbf /oracle/oradata/aux/system.dbf
       cp /backup/users01.dbf /oracle/oradata/aux/users01.dbf
       cp /backup/tools01.dbf /oracle/oradata/aux/tools01.dbf

2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:

       STARTUP NOMOUNT PFILE=/aux/initAUX.ora

3. Mount the auxiliary database, specifying the `CLONE` keyword:

       ALTER DATABASE MOUNT CLONE DATABASE;

   The `CLONE` keyword causes Oracle to take all datafiles offline automatically.

4. Manually rename all auxiliary database files to reflect their new locations only if these files are not renamed by `DB_FILE_NAME_CONVERT` and `LOG_FILE_NAME_CONVERT`. In our scenario, all datafiles and online redo logs are renamed by initialization parameters, so no manual renaming is necessary.

5. Run the following SQL script on the auxiliary database to ensure that all datafiles are named correctly:

       SELECT NAME FROM V$DATAFILE
       UNION ALL
       SELECT MEMBER FROM V$LOGFILE
       UNION ALL
       SELECT NAME FROM V$CONTROLFILE
       /

   If not, then rename the files manually as in the previous step.

6. Bring only the datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:

       ALTER DATABASE DATAFILE /oracle/oradata/aux/system.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/aux/users01.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/aux/tools01.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/aux/undo01.dbf ONLINE;

   Note: The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

   At this point, the auxiliary database is mounted and ready for media recovery.

7. Recover the auxiliary database to the specified point in time with the `USING` `BACKUP` `CONTROLFILE` option. Use any form of incomplete recovery. The following example uses cancel-based incomplete recovery:

       RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

8. Open the auxiliary database with the `RESETLOGS` option using the following statement:

       ALTER DATABASE OPEN RESETLOGS;

## Restoring the Auxiliary Database on a Different Host with the Same Path Names

The following example assumes that you create the auxiliary database on a different host called `aux_host`. The auxiliary host has the same path names as the primary host. Hence, you do not need to rename the auxiliary database datafiles. So, you do not need to set `DB_FILE_NAME_CONVERT`, although you should set `LOG_FILE_NAME_CONVERT`.

To restore and recover the auxiliary database:

1. Restore the auxiliary set and the recovery set to the auxiliary host. For example, assume that the auxiliary set consists of the following files:

       /oracle/oradata/control01.dbf      # control file
       /oracle/oradata/undo01.dbf     # datafile in undo tablespace
       /oracle/oradata/system.dbf   # datafile in SYSTEM tablespace

   And the recovery set consists of the following datafiles:

       /oracle/oradata/users01.dbf  # 1st datafile in users tablespace
       /oracle/oradata/tools01.dbf  # 2nd datafile in tools tablespace

   These files will occupy the same locations in the auxiliary host.

2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:

       STARTUP NOMOUNT PFILE=/aux/initAUX.ora

3. Mount the auxiliary database, specifying the `CLONE` keyword:

       ALTER DATABASE MOUNT CLONE DATABASE;

   The `CLONE` keyword causes Oracle to take all datafiles offline automatically.

4. Rename all auxiliary database files to reflect their new locations only if these files are not renamed by `DB_FILE_NAME_CONVERT` and `LOG_FILE_NAME_CONVERT`. In our scenario, the datafiles do not require renaming, and the logs are converted with `LOG_FILE_NAME_CONVERT`. So, no manual renaming is necessary.

5. Run the following script in SQL*Plus on the auxiliary database to ensure that all datafiles are named correctly.

       SELECT NAME FROM V$DATAFILE
       UNION ALL
       SELECT MEMBER FROM V$LOGFILE
       UNION ALL
       SELECT NAME FROM V$CONTROLFILE
       ;

   If not, then rename them manually as in the previous step.

6. Bring all datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:

       ALTER DATABASE DATAFILE /oracle/oradata/system.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/users01.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/tools01.dbf ONLINE;
       ALTER DATABASE DATAFILE /oracle/oradata/undo01.dbf ONLINE;

   Note: The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

   At this point, the auxiliary database is mounted and ready for media recovery.

7. Recover the auxiliary database to the specified point in time with the `USING` `BACKUP` `CONTROLFILE` option. Use any form of incomplete recovery. The following example uses cancel-based incomplete recovery:

       RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

8. Open the auxiliary database with the `RESETLOGS` option using the following statement:

       ALTER DATABASE OPEN RESETLOGS;

## Restoring the Auxiliary Database on a Different Host with Different Path Names

This case should be treated exactly like "Restoring and Recovering the Auxiliary Database on the Same Host". The same guidelines for renaming files apply in both cases.